﻿Imports Microsoft.Office.Core
Imports Microsoft.Office.Interop
Imports System.Data.SqlClient

Public Class frmMain
    Private Structure Table
        Dim DT As DataTable
        Dim DA As SqlDataAdapter
    End Structure

    Dim Connection As SqlConnection

    Dim DS As DataSet
    Dim PAYs As Table
    Dim LISTs As Table
    Dim BANKs As Table

    Dim CurrentRow As String

    Private Function CreateTable(ByVal strSelect As String) As Table
        'Создаёт DataAdapter
        'Затём из Select находит команды insert, delete, update
        'Создаёт DataTable и заполняет его данными из DataAdapter
        With CreateTable
            .DA = New SqlDataAdapter(strSelect, Connection)
            Dim BLD As New SqlCommandBuilder(.DA)
            .DA.InsertCommand = BLD.GetInsertCommand
            .DA.DeleteCommand = BLD.GetDeleteCommand
            .DA.UpdateCommand = BLD.GetUpdateCommand
            .DT = New DataTable
            .DA.FillSchema(.DT, SchemaType.Mapped)
            .DA.Fill(.DT)
            DS.Tables.Add(.DT)
        End With
    End Function

    Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.Show()
        State.Text = "Загрузка..."
        Dim ConnectionString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & Application.StartupPath & "\data\database.mdf;Integrated Security=True;User Instance=True"
        Connection = New SqlConnection(ConnectionString)
        Try
            Connection.Open()
        Catch ex As Exception
            MsgBox("Ошибка во время открытия соединения с БД" & vbNewLine & "Строка соединения:" & vbNewLine & ConnectionString, MsgBoxStyle.Critical, "Error")
            End
        End Try

        DS = New DataSet

        PAYs = CreateTable("SELECT * FROM PAYs")
        LISTs = CreateTable("SELECT * FROM LISTs")
        DS.Relations.Add("Lists2Pays", LISTs.DT.Columns("id"), PAYs.DT.Columns("List"))

        BANKs = CreateTable("SELECT * FROM BANKs")

        dgvLists.DataSource = DS
        dgvLists.DataMember = "Lists"

        dgvPays.DataSource = DS
        dgvPays.DataMember = "Lists.Lists2Pays"
        State.Text = "Готово"

    End Sub

#Region "При Нажатии на кнопку Cохранить в тулбарах"
    '--------------------------------------------------------------------------------------------
    'При нажатии на кнопку сохранить в ToolBar-ах рядом с DataGridView
    Private Sub PAYs_SaveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PAYs_SaveButton.Click
        Me.Validate()
        dgvPays.EndEdit()
        PAYs.DA.Update(PAYs.DT)
        PAYs.DT.AcceptChanges()

        PAYs_SaveButton.Enabled = False

        dgvLists.CurrentRow.Cells("Количество").Value = DS.Tables("Pays").Compute("COUNT(ID)", "List=" & CurrentRow)
        dgvLists.CurrentRow.Cells("Сумма").Value = DS.Tables("Pays").Compute("SUM(Сумма)", "List=" & CurrentRow)
    End Sub
    Private Sub LISTs_SaveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LISTs_SaveButton.Click
        Me.Validate()
        dgvLists.EndEdit()
        LISTs.DA.Update(LISTs.DT)
        LISTs.DT.AcceptChanges()

        LISTs_SaveButton.Enabled = False
    End Sub
#End Region
#Region "При Нажатии на кнопку Создать в тулбарах"
    Private Sub LISTs_CreateNewButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LISTs_CreateNewButton.Click
        LISTs.DT.Rows.Add()
        LISTs_SaveButton.Enabled = True
    End Sub
    Private Sub PAYs_CreateNewButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PAYs_CreateNewButton.Click
        PAYs.DT.Rows.Add().Item("LIST") = CurrentRow
        PAYs_SaveButton.Enabled = True
    End Sub
#End Region
#Region "Печать PAYs (в Excel)"
    Private Sub ПечатьToolStripButton_Click(sender As System.Object, e As System.EventArgs) Handles ПечатьToolStripButton.Click
        Dim oXL As New Excel.Application
        Dim oWB As Excel.Workbook = oXL.Workbooks.Add
        Dim oST As Excel.Worksheet = oWB.ActiveSheet
        oXL.Visible = True

        Dim StartRow As Integer = 2
        Dim StartCol As Integer = 1
        With oST
            .Cells(1, 1).Value = "№ п/п"
            .Cells(1, 2).Value = "Счёт"
            .Cells(1, 3).Value = "Фамилия"
            .Cells(1, 4).Value = "Имя"
            .Cells(1, 5).Value = "Отчество"
            .Cells(1, 6).Value = "Сумма"
            Dim i As Integer
            For i = 0 To dgvPays.Rows.Count - 1
                .Cells(StartRow + i, 1).Value = i + 1
                .Cells(StartRow + i, 2).NumberFormat = "@"
                .Cells(StartRow + i, 2).Value = dgvPays.Rows(i).Cells("Счёт").Value
                .Cells(StartRow + i, 3).Value = dgvPays.Rows(i).Cells("Фамилия").Value
                .Cells(StartRow + i, 4).Value = dgvPays.Rows(i).Cells("Имя").Value
                .Cells(StartRow + i, 5).Value = dgvPays.Rows(i).Cells("Отчество").Value
                .Cells(StartRow + i, 6).Value = dgvPays.Rows(i).Cells("Сумма").Value
            Next i
            .Cells(StartRow + i, 6).Value = DS.Tables("Pays").Compute("SUM(Сумма)", "LIST=" & CurrentRow)
        End With
    End Sub
#End Region


    Private Sub dgvLists_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgvLists.SelectionChanged
        Try
            CurrentRow = dgvLists.CurrentRow.Cells("ID").Value
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub


    Private Sub dgvPays_CellValueChanged(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvPays.CellValueChanged
        PAYs_SaveButton.Enabled = True
    End Sub

    Private Sub dgvLists_CellValueChanged(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvLists.CellValueChanged
        LISTs_SaveButton.Enabled = True
    End Sub

    Private Sub PAYs_OpenButton_Click(sender As System.Object, e As System.EventArgs) Handles PAYs_OpenButton.Click
        LoadFromFileContextMenu.Show(Cursor.Position.X, Cursor.Position.Y)
    End Sub

    Private Sub mnuLoadOpenFile_Click(sender As System.Object, e As System.EventArgs) Handles mnuLoadOpenFile.Click
        If OFD.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Dim hFile As System.IO.StreamReader
            Dim strLine As String
            hFile = My.Computer.FileSystem.OpenTextFileReader(OFD.FileName, System.Text.Encoding.GetEncoding(866))
            Dim line As String()
            Do Until hFile.EndOfStream
                strLine = hFile.ReadLine()
                line = strLine.Split("|")
                With PAYs.DT.Rows.Add()
                    .Item("LIST") = CurrentRow
                    .Item("Счёт") = line(1)
                    .Item("Фамилия") = line(3)
                    .Item("Имя") = line(4)
                    .Item("Отчество") = line(5)
                    .Item("Сумма") = Replace(line(2), ".", ",")
                End With
            Loop
        End If
        PAYs_SaveButton.Enabled = True
    End Sub

    Private Sub tsPaysExportSBRF_Click(sender As System.Object, e As System.EventArgs) Handles tsPaysExportSBRF.Click

        Dim ExportTableName As String = "t4271000"
        Dim ExportFileName As String = ExportTableName & ".DBF"

        Dim src As String = Application.StartupPath & "\DATA\BANK_SBRF.DBF"
        Dim dst As String = Application.StartupPath & "\OUTPUT\" & ExportFileName

        Try
            MkDir(Application.StartupPath & "\OUTPUT\")
        Catch ex As Exception
        End Try

        If FileIO.FileSystem.FileExists(dst) Then
            If MsgBox("Файл уже существует. Заменить файл " & dst & "?") = MsgBoxResult.No Then Exit Sub
        End If

        FileCopy(src, dst)
        ExportPAYsToDBF(ExportTableName)

    End Sub

    Private Sub ExportPAYsToDBF(ExportTableName As String)

        Dim ExportFileName As String = ExportTableName & ".DBF"
        Dim ExportConnection As OleDb.OleDbConnection
        Dim ExportConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\OUTPUT\" & ";Extended Properties=dBASE IV"
        ExportConnection = New OleDb.OleDbConnection(ExportConnectionString)
        ExportConnection.Open()

        Dim INS As New OleDb.OleDbCommand("INSERT INTO " & ExportTableName & " VALUES (?,?,?,?,?,?,?)", ExportConnection)
        With INS.Parameters
            Dim i As Integer
            For i = 0 To 6
                INS.Parameters.Add(INS.CreateParameter)
            Next

            'первая строка содержит только номер отделения банка
            .Item(0).Value = "4271"
            .Item(1).Value = ""
            .Item(2).Value = ""
            .Item(3).Value = ""
            .Item(4).Value = ""
            .Item(5).Value = ""
            .Item(6).Value = ""

            INS.ExecuteNonQuery()

            'вторая строка содержит номер и дату платёжного поручения
            .Item(0).Value = "К платежному поручению #"
            .Item(1).Value = ""
            .Item(2).Value = "от"
            .Item(3).Value = ""
            INS.ExecuteNonQuery()

            'третья строка содержит тип зачисления
            .Item(0).Value = "Зачисление"
            .Item(1).Value = "07"
            .Item(2).Value = ""
            .Item(3).Value = ""
            INS.ExecuteNonQuery()

            'четвёртая строка содержит номер и дату договора
            .Item(0).Value = "По договору:"
            .Item(1).Value = ""
            .Item(2).Value = "от"
            .Item(3).Value = ""
            INS.ExecuteNonQuery()

            'пятая строка содержит заголовки полей
            .Item(0).Value = "# п/п"
            .Item(1).Value = "Номер счета"
            .Item(2).Value = "Фамилия"
            .Item(3).Value = "Имя"
            .Item(4).Value = "Отчество"
            .Item(5).Value = "Сумма"
            .Item(6).Value = "Примечание"
            INS.ExecuteNonQuery()

            .Item(6).Value = ""
            'дальше идёт сам список зачислений
            For i = 0 To dgvPays.Rows.Count - 1
                .Item(0).Value = i + 1
                .Item(1).Value = dgvPays.Rows(i).Cells("Счёт").Value
                .Item(2).Value = dgvPays.Rows(i).Cells("Фамилия").Value
                .Item(3).Value = dgvPays.Rows(i).Cells("Имя").Value
                .Item(4).Value = dgvPays.Rows(i).Cells("Отчество").Value
                .Item(5).Value = Replace(Format(dgvPays.Rows(i).Cells("Сумма").Value, "0.00"), ",", ".")
                INS.ExecuteNonQuery()
            Next i

            'самая последняя строка содержит "Итого"
            .Item(0).Value = ""
            .Item(1).Value = "Итого:"
            .Item(2).Value = ""
            .Item(3).Value = ""
            .Item(4).Value = ""
            .Item(5).Value = Replace(Format(DS.Tables("Pays").Compute("SUM(Сумма)", "LIST=" & CurrentRow), "0.00"), ",", ".")
            INS.ExecuteNonQuery()
        End With

        ExportConnection.Close()

        MsgBox("Сформирован файл " & ExportFileName & ".")
    End Sub

    Private Sub tsPaysExport_Click(sender As System.Object, e As System.EventArgs) Handles tsPaysExport.Click
        mnuExport.Show(Cursor.Position.X, Cursor.Position.Y)
    End Sub
End Class
